package com.geek.projects.user.sql;

import com.geek.projects.user.domain.User;

import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;

public class DBConnectionManager {

    private Connection connection;

    public DBConnectionManager(Connection connection) {
        this.connection = connection;
    }


    public Connection getConnection() {
        return this.connection;
    }

    public void releaseConnection() {
        if (this.connection != null) {
            try {
                this.connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e.getCause());
            }
        }
    }


    public static final String DROP_USERS_TABLE_DDL_SQL = "DROP TABLE users";

    public static final String CREATE_USERS_TABLE_DDL_SQL = "CREATE TABLE users(" +
            "id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), " +
            "name VARCHAR(16) NOT NULL, " +
            "password VARCHAR(64) NOT NULL, " +
            "email VARCHAR(64) NOT NULL, " +
            "phoneNumber VARCHAR(64) NOT NULL" +
            ")";


    public static final String INSERT_USER_DML_SQL = "INSERT INTO users(name,password,email,phoneNumber) VALUES " +
            "('A','******','a@gmail.com','1') , " +
            "('B','******','b@gmail.com','2') , " +
            "('C','******','c@gmail.com','3') , " +
            "('D','******','d@gmail.com','4') , " +
            "('E','******','e@gmail.com','5')";


    public static void main(String[] args) throws SQLException, IntrospectionException, NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        String databaseURL = "jdbc:derby:user-platform;create=true";
        Connection connection = DriverManager.getConnection(databaseURL);
        Statement statement = connection.createStatement();
        //System.out.println(statement.execute(DROP_USERS_TABLE_DDL_SQL));
        System.out.println(statement.execute(CREATE_USERS_TABLE_DDL_SQL)); // false
        //System.out.println(statement.executeUpdate(INSERT_USER_DML_SQL));  // 5
        ResultSet resultSet = statement.executeQuery(" SELECT id,name,password,email,phoneNumber FROM users ");
        ResultSetMetaData metaData = resultSet.getMetaData();
        System.out.println("当前表的名称：" + metaData.getTableName(1));
        System.out.println("当前表的列个数：" + metaData.getColumnCount());
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            System.out.println("列名称：" + metaData.getColumnLabel(i) + ", 类型：" + metaData.getColumnClassName(i));
        }
        StringBuilder queryAllUsersSQLBuilder = new StringBuilder("SELECT");
        for (int i = 1; i <= metaData.getColumnCount(); i++) {
            queryAllUsersSQLBuilder.append(" ").append(metaData.getColumnLabel(i)).append(",");
        }
        // 移除最后一个 ","
        queryAllUsersSQLBuilder.deleteCharAt(queryAllUsersSQLBuilder.length() - 1);
        queryAllUsersSQLBuilder.append(" FROM ").append(metaData.getTableName(1));
        System.out.println(queryAllUsersSQLBuilder);

        // BeanInfo
        BeanInfo userBeanInfo = Introspector.getBeanInfo(User.class, Object.class);
        PropertyDescriptor[] propertyDescriptors = userBeanInfo.getPropertyDescriptors();

        while (resultSet.next()) {
            User user = new User();
            for (PropertyDescriptor propertyDescriptor : propertyDescriptors) {
                String fieldName = propertyDescriptor.getName();
                Class<?> fieldType = propertyDescriptor.getPropertyType();
                String methodName = typeMethodMappings.get(fieldType);
                // 可能存在映射关系（不过此处是相等的）
                String columnLabel = mapColumnLabel(fieldName);
                Method resultSetMethod = ResultSet.class.getMethod(methodName, String.class);
                Object resultValue = resultSetMethod.invoke(resultSet, columnLabel);
                Method writeMethod = propertyDescriptor.getWriteMethod();
                writeMethod.invoke(user, resultValue);
            }

            System.out.println(user);

            /*

            User user = new User();
            // 方法直接调用（编译时，生成字节码）
            user.setId(resultSet.getLong("id"));
            user.setName(resultSet.getString("name"));
            user.setPassword(resultSet.getString("password"));
            user.setEmail(resultSet.getString("email"));
            user.setPhoneNumber(resultSet.getString("phoneNumber"));
            System.out.println(user);*/
        }
    }

    private static String mapColumnLabel(String fieldName) {
        return fieldName;
    }


    /**
     * 数据类型与 ResultSet 方法名映射
     */
    static Map<Class, String> typeMethodMappings = new HashMap<>();

    static {
        typeMethodMappings.put(Long.class, "getLong");
        typeMethodMappings.put(String.class, "getString");
    }

}
